package org.example.zuoye.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author xinchen
 * @Date: 2024/08/17/ 20:56
 * @Description
 */
@Mapper
public interface ChartsMapper {
    /**
     * 获取患者总数
     * @return
     */
    @Select({"SELECT COUNT(*) AS total FROM patient",})
    Map<String, Object> getPatientTotal();

    /**
     * 获取医生总数
     * @return
     */
    @Select({"SELECT COUNT(*) AS total FROM doctor",})
    Map<String, Object> getDoctorTotal();

    /**
     * 获取放号总数
     * @return
     */
    @Select({"SELECT sum(total_slots) AS total FROM schedules",})
    Map<String, Object> getScheduleTotal();

    /**
     * 获取预约总数
     * @return
     */
    @Select({"SELECT COUNT(*) AS total FROM appoint",})
    Map<String, Object> getAppointTotal();

    /**
     * 查询医生放号情况和医生姓名，并排序
     * @param date "WHERE s.start_time > #{date}",
     * @return
     */
    @Select({
            "SELECT d.name, SUM(s.total_slots) AS total",
            "FROM doctor d",
            "left JOIN schedules s ON d.doc_id = s.doc_id",
             "WHERE s.start_time > #{date}",
            "GROUP BY d.doc_id",
            "ORDER BY total DESC",
            "LIMIT 10"
    })
    List<Map<String, Object>> selectDoctorOrderByScheduleCount(Date date);


    /**
     * 热门预约医生
     * @param date
     * @return
     */
    @Select({
            "SELECT ",
            "    d.name AS name, ",
            "    COUNT(a.app_id) AS count ",
            "FROM ",
            "    doctor AS d ",
            "LEFT JOIN ",
            "    schedules AS sc ON d.doc_id = sc.doc_id ",
            "LEFT JOIN ",
            "    appoint AS a ON sc.schedule_id = a.schedule_id ",
            "GROUP BY ",
            "    d.name ",
            "ORDER BY ",
            "    count DESC ",
            "LIMIT 10"
    })
    List<Map<String, Object>> selectApponinOrderBydoctorCount(Date date);


    @Select("SELECT date(a.appoint_time) AS day, COUNT(a.appoint_time) AS count FROM doctor AS d LEFT JOIN schedules AS sc ON d.doc_id = sc.doc_id LEFT JOIN appoint AS a ON sc.schedule_id = a.schedule_id WHERE a.appoint_time > #{date} GROUP BY date(a.appoint_time) ORDER BY date(a.appoint_time)")
    List<Map<String, Object>> getAppointCount(Date date);

    /**
     * 获取近7天放号情况
     * @param date
     * @return
     */
    @Select("SELECT date(start_time) AS day, COUNT(0) AS count FROM schedules WHERE start_time > #{date} GROUP BY date(start_time) ORDER BY date(start_time)")
    List<Map<String, Object>> getWeekSchedule(Date date);


    /**
     * 每周每天的预约数量
     * @param date
     * @return
     */
    @Select("SELECT date(a.appoint_time) AS day, COUNT(a.app_id) AS count FROM appoint AS a WHERE a.appoint_time > #{date} GROUP BY date(a.appoint_time) ORDER BY date(a.appoint_time)")
    List<Map<String, Object>> getWeekAppoint(Date date);
}
